{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  创建字典"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one_name</th>\n",
       "      <th>two_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one_name  two_name\n",
       "0         1         4\n",
       "1         2         5\n",
       "2         3         6"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\"one_name\":[1,2,3],\"two_name\":[4,5,6]})\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  将以上内容写入到CSV的格式的文件存放在当前文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\":\\test.txt\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 打开这个文件 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "file = open(':\\test.txt')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读取CSV文件信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>one_name</th>\n",
       "      <th>two_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  one_name  two_name\n",
       "0           0         1         4\n",
       "1           1         2         5\n",
       "2           2         3         6"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_data= pd.read_csv(file)\n",
    "file_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>col1</th>\n",
       "      <th>col2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>数</td>\n",
       "      <td>分</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>据</td>\n",
       "      <td>析</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col1 col2\n",
       "0    数    分\n",
       "1    据    析"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.DataFrame({\"col1\":[\"数\",\"据\"],\"col2\":[\"分\",\"析\"]})\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 写入到Excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.to_excel(\":\\test.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_path=\":\\test.xlsx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>col1</th>\n",
       "      <th>col2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>数</td>\n",
       "      <td>分</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>据</td>\n",
       "      <td>析</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0 col1 col2\n",
       "0           0    数    分\n",
       "1           1    据    析"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data=pd.read_excel(excel_path)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 爬取该网站内的表格信息  ps：不能用“”，会报错"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "html=requests.get('http://kaoshi.edu.sina.com.cn/college/majorlist')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读取该网站的信息并以utf-8的格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>专业名称</td>\n",
       "      <td>专业代码</td>\n",
       "      <td>专业大类</td>\n",
       "      <td>专业小类</td>\n",
       "      <td>操作</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>哲学类</td>\n",
       "      <td>0101</td>\n",
       "      <td>哲学</td>\n",
       "      <td>哲学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>哲学</td>\n",
       "      <td>010101</td>\n",
       "      <td>哲学</td>\n",
       "      <td>哲学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>逻辑学</td>\n",
       "      <td>010102</td>\n",
       "      <td>哲学</td>\n",
       "      <td>哲学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>宗教学</td>\n",
       "      <td>010103</td>\n",
       "      <td>哲学</td>\n",
       "      <td>哲学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>伦理学</td>\n",
       "      <td>010104</td>\n",
       "      <td>哲学</td>\n",
       "      <td>哲学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>经济学类</td>\n",
       "      <td>0201</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>经济学</td>\n",
       "      <td>020101</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>经济统计学</td>\n",
       "      <td>020102</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>国民经济管理</td>\n",
       "      <td>020103</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>资源与环境经济学</td>\n",
       "      <td>020104</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>商务经济学</td>\n",
       "      <td>020105</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>能源经济</td>\n",
       "      <td>020106</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>劳动经济学</td>\n",
       "      <td>020107</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>经济工程</td>\n",
       "      <td>020108</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>数字经济</td>\n",
       "      <td>020109</td>\n",
       "      <td>经济学</td>\n",
       "      <td>经济学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>财政学类</td>\n",
       "      <td>0202</td>\n",
       "      <td>经济学</td>\n",
       "      <td>财政学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>财政学</td>\n",
       "      <td>020201</td>\n",
       "      <td>经济学</td>\n",
       "      <td>财政学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>税收学</td>\n",
       "      <td>020202</td>\n",
       "      <td>经济学</td>\n",
       "      <td>财政学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>金融学类</td>\n",
       "      <td>0203</td>\n",
       "      <td>经济学</td>\n",
       "      <td>金融学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>金融学</td>\n",
       "      <td>020301</td>\n",
       "      <td>经济学</td>\n",
       "      <td>金融学类</td>\n",
       "      <td>开设院校 加入对比</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0       1     2     3          4\n",
       "0       专业名称    专业代码  专业大类  专业小类         操作\n",
       "1        哲学类    0101    哲学   哲学类  开设院校 加入对比\n",
       "2         哲学  010101    哲学   哲学类  开设院校 加入对比\n",
       "3        逻辑学  010102    哲学   哲学类  开设院校 加入对比\n",
       "4        宗教学  010103    哲学   哲学类  开设院校 加入对比\n",
       "5        伦理学  010104    哲学   哲学类  开设院校 加入对比\n",
       "6       经济学类    0201   经济学  经济学类  开设院校 加入对比\n",
       "7        经济学  020101   经济学  经济学类  开设院校 加入对比\n",
       "8      经济统计学  020102   经济学  经济学类  开设院校 加入对比\n",
       "9     国民经济管理  020103   经济学  经济学类  开设院校 加入对比\n",
       "10  资源与环境经济学  020104   经济学  经济学类  开设院校 加入对比\n",
       "11     商务经济学  020105   经济学  经济学类  开设院校 加入对比\n",
       "12      能源经济  020106   经济学  经济学类  开设院校 加入对比\n",
       "13     劳动经济学  020107   经济学  经济学类  开设院校 加入对比\n",
       "14      经济工程  020108   经济学  经济学类  开设院校 加入对比\n",
       "15      数字经济  020109   经济学  经济学类  开设院校 加入对比\n",
       "16      财政学类    0202   经济学  财政学类  开设院校 加入对比\n",
       "17       财政学  020201   经济学  财政学类  开设院校 加入对比\n",
       "18       税收学  020202   经济学  财政学类  开设院校 加入对比\n",
       "19      金融学类    0203   经济学  金融学类  开设院校 加入对比\n",
       "20       金融学  020301   经济学  金融学类  开设院校 加入对比"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "html_table=pd.read_html(html.content,encoding=\"utf-8\")\n",
    "html_table[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 案例： 北京高考分数线统计分析"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "具体要求： (http://gaokao.xdf.cn/201805/10784342.html)\n",
    "<li>（1）：一本文理科与二本文理科最高分数线是多少，最低的分数线是多少，相差多少？\n",
    "<li>（2）：2018年与2017年相比，一本文理科与二本文理科比变化多少分？\n",
    "<li>（3）：求2006-2018年近13年每科分数线的平均值？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>一本分数线</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>二本分数线</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>文科</td>\n",
       "      <td>理科</td>\n",
       "      <td>文科</td>\n",
       "      <td>理科</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>576</td>\n",
       "      <td>532</td>\n",
       "      <td>488</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>555</td>\n",
       "      <td>537</td>\n",
       "      <td>468</td>\n",
       "      <td>439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016.0</td>\n",
       "      <td>583</td>\n",
       "      <td>548</td>\n",
       "      <td>532</td>\n",
       "      <td>494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015.0</td>\n",
       "      <td>579</td>\n",
       "      <td>548</td>\n",
       "      <td>527</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2014.0</td>\n",
       "      <td>565</td>\n",
       "      <td>543</td>\n",
       "      <td>507</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013.0</td>\n",
       "      <td>549</td>\n",
       "      <td>550</td>\n",
       "      <td>494</td>\n",
       "      <td>505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2012.0</td>\n",
       "      <td>495</td>\n",
       "      <td>477</td>\n",
       "      <td>446</td>\n",
       "      <td>433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011.0</td>\n",
       "      <td>524</td>\n",
       "      <td>484</td>\n",
       "      <td>481</td>\n",
       "      <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2010.0</td>\n",
       "      <td>524</td>\n",
       "      <td>494</td>\n",
       "      <td>474</td>\n",
       "      <td>441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2009.0</td>\n",
       "      <td>532</td>\n",
       "      <td>501</td>\n",
       "      <td>489</td>\n",
       "      <td>459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2008.0</td>\n",
       "      <td>515</td>\n",
       "      <td>502</td>\n",
       "      <td>472</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2007.0</td>\n",
       "      <td>528</td>\n",
       "      <td>531</td>\n",
       "      <td>489</td>\n",
       "      <td>478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2006.0</td>\n",
       "      <td>516</td>\n",
       "      <td>528</td>\n",
       "      <td>476</td>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0 一本分数线 Unnamed: 2 二本分数线 Unnamed: 4\n",
       "0          NaN    文科         理科    文科         理科\n",
       "1       2018.0   576        532   488        432\n",
       "2       2017.0   555        537   468        439\n",
       "3       2016.0   583        548   532        494\n",
       "4       2015.0   579        548   527        495\n",
       "5       2014.0   565        543   507        495\n",
       "6       2013.0   549        550   494        505\n",
       "7       2012.0   495        477   446        433\n",
       "8       2011.0   524        484   481        435\n",
       "9       2010.0   524        494   474        441\n",
       "10      2009.0   532        501   489        459\n",
       "11      2008.0   515        502   472        455\n",
       "12      2007.0   528        531   489        478\n",
       "13      2006.0   516        528   476        476"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = open('scores.xlsx')\n",
    "path= 'scores.xlsx'\n",
    "data=pd.read_excel(path)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "data_table=pd.read_excel(path)\n",
    "data_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### （1）：一本文理科与二本文理科最高分数线是多少，最低的分数线是多少，相差多少？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  一本文科分数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "一本文科最高分： 583\n",
      "一本文科最低分 495\n"
     ]
    }
   ],
   "source": [
    "sorce=data['一本分数线'][1:]\n",
    "wk1max=sorce.max()\n",
    "wk1min=sorce.min()\n",
    "print(\"一本文科最高分：\",sorce.max())\n",
    "print('一本文科最低分',sorce.min())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 一本理科分数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "一本理科最高分： 550\n",
      "一本理科最低分： 477\n"
     ]
    }
   ],
   "source": [
    "sorce=data['Unnamed: 2'][1:]\n",
    "lk1max=sorce.max()\n",
    "lk1min=sorce.min()\n",
    "print(\"一本理科最高分：\",sorce.max())\n",
    "print('一本理科最低分：',sorce.min())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 二本文科分数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "一本理科最高分： 532\n",
      "一本理科最低分： 446\n"
     ]
    }
   ],
   "source": [
    "sorce=data_table['二本分数线'][1:]\n",
    "wk2max=sorce.max()\n",
    "wk2min=sorce.min()\n",
    "print(\"一本理科最高分：\",sorce.max())\n",
    "print('一本理科最低分：',sorce.min())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 二本理科分数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "二本理科最高分： 505\n",
      "一本理科最低分： 432\n"
     ]
    }
   ],
   "source": [
    "sorce=data_table['Unnamed: 4'][1:]\n",
    "lk2max=sorce.max()\n",
    "lk2min=sorce.min()\n",
    "print(\"二本理科最高分：\",sorce.max())\n",
    "print('一本理科最低分：',sorce.min())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####   一本二本文科相差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "一本二本文科最大值相差： 51\n",
      "一本二本文科最小值相差： 49\n"
     ]
    }
   ],
   "source": [
    "sorce=data_table['二本分数线'][1:]\n",
    "print(\"一本二本文科最大值相差：\",wk1max-wk2max)\n",
    "print(\"一本二本文科最小值相差：\",wk1min-wk2min)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 一本二本理科相差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "一本二本理科最大值相差： 45\n",
      "一本二本理科最小值相差： 45\n"
     ]
    }
   ],
   "source": [
    "sorce=data_table['二本分数线'][1:]\n",
    "print(\"一本二本理科最大值相差：\",lk1max-lk2max)\n",
    "print(\"一本二本理科最小值相差：\",lk1min-lk2min)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### （2）：2018年与2017年相比，一本文理科与二本文理科比变化多少分？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>一本分数线</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>二本分数线</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>576</td>\n",
       "      <td>532</td>\n",
       "      <td>488</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017.0</td>\n",
       "      <td>555</td>\n",
       "      <td>537</td>\n",
       "      <td>468</td>\n",
       "      <td>439</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0 一本分数线 Unnamed: 2 二本分数线 Unnamed: 4\n",
       "1      2018.0   576        532   488        432\n",
       "2      2017.0   555        537   468        439"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "two_year=data_table[1:3]\n",
    "two_year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2018和2017一本二本文理科相差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "一本文科分数相差： 21\n",
      "一本理科分数相差： -5\n",
      "二本文科分数相差： 20\n",
      "二本理科分数相差： -7\n"
     ]
    }
   ],
   "source": [
    "print(\"一本文科分数相差：\",two_year.loc[1]['一本分数线']-two_year.loc[2]['一本分数线'])\n",
    "print(\"一本理科分数相差：\",two_year.loc[1]['Unnamed: 2']-two_year.loc[2]['Unnamed: 2'])\n",
    "print(\"二本文科分数相差：\",two_year.loc[1]['二本分数线']-two_year.loc[2]['二本分数线'])\n",
    "print(\"二本理科分数相差：\",two_year.loc[1]['Unnamed: 4']-two_year.loc[2]['Unnamed: 4'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  （3）：求2006-2018年近13年每科分数线的平均值？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean=data_table[1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2006-2018年近13年每科分数线的平均值:\n",
      "一本文科平均分： 541.6153846153846\n",
      "一本理科平均分： 521.1538461538462\n",
      "二本文科平均分： 487.9230769230769\n",
      "二本理科平均分： 464.38461538461536\n"
     ]
    }
   ],
   "source": [
    "print(\"2006-2018年近13年每科分数线的平均值:\")\n",
    "print('一本文科平均分：',mean['一本分数线'].mean())\n",
    "print('一本理科平均分：',mean['Unnamed: 2'].mean())\n",
    "print('二本文科平均分：',mean['二本分数线'].mean())\n",
    "print('二本理科平均分：',mean['Unnamed: 4'].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 另一种方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0_level_0</th>\n",
       "      <th colspan=\"2\" halign=\"left\">一本分数线</th>\n",
       "      <th colspan=\"2\" halign=\"left\">二本分数线</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0_level_1</th>\n",
       "      <th>文科</th>\n",
       "      <th>理科</th>\n",
       "      <th>文科</th>\n",
       "      <th>理科</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018</td>\n",
       "      <td>576</td>\n",
       "      <td>532</td>\n",
       "      <td>488</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>555</td>\n",
       "      <td>537</td>\n",
       "      <td>468</td>\n",
       "      <td>439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>583</td>\n",
       "      <td>548</td>\n",
       "      <td>532</td>\n",
       "      <td>494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>579</td>\n",
       "      <td>548</td>\n",
       "      <td>527</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014</td>\n",
       "      <td>565</td>\n",
       "      <td>543</td>\n",
       "      <td>507</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013</td>\n",
       "      <td>549</td>\n",
       "      <td>550</td>\n",
       "      <td>494</td>\n",
       "      <td>505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2012</td>\n",
       "      <td>495</td>\n",
       "      <td>477</td>\n",
       "      <td>446</td>\n",
       "      <td>433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011</td>\n",
       "      <td>524</td>\n",
       "      <td>484</td>\n",
       "      <td>481</td>\n",
       "      <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2010</td>\n",
       "      <td>524</td>\n",
       "      <td>494</td>\n",
       "      <td>474</td>\n",
       "      <td>441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2009</td>\n",
       "      <td>532</td>\n",
       "      <td>501</td>\n",
       "      <td>489</td>\n",
       "      <td>459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2008</td>\n",
       "      <td>515</td>\n",
       "      <td>502</td>\n",
       "      <td>472</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2007</td>\n",
       "      <td>528</td>\n",
       "      <td>531</td>\n",
       "      <td>489</td>\n",
       "      <td>478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2006</td>\n",
       "      <td>516</td>\n",
       "      <td>528</td>\n",
       "      <td>476</td>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0_level_0 一本分数线      二本分数线     \n",
       "   Unnamed: 0_level_1    文科   理科    文科   理科\n",
       "0                2018   576  532   488  432\n",
       "1                2017   555  537   468  439\n",
       "2                2016   583  548   532  494\n",
       "3                2015   579  548   527  495\n",
       "4                2014   565  543   507  495\n",
       "5                2013   549  550   494  505\n",
       "6                2012   495  477   446  433\n",
       "7                2011   524  484   481  435\n",
       "8                2010   524  494   474  441\n",
       "9                2009   532  501   489  459\n",
       "10               2008   515  502   472  455\n",
       "11               2007   528  531   489  478\n",
       "12               2006   516  528   476  476"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = open('scores.xlsx')\n",
    "path= 'scores.xlsx'\n",
    "data=pd.read_excel(path,header=[0,1],index=[0])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "can't assign to function call (<ipython-input-192-471ca882a4f0>, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-192-471ca882a4f0>\"\u001b[1;36m, line \u001b[1;32m1\u001b[0m\n\u001b[1;33m    score_max()=max(data[\"一本分数线\"][\"文科\"])\u001b[0m\n\u001b[1;37m                                        ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m can't assign to function call\n"
     ]
    }
   ],
   "source": [
    "score_max()=max(data[\"一本分数线\"][\"文科\"])\n",
    "print(\"文科最高分：\",score_max())\n",
    "score_min()=min(data[\"一本分数线\"][\"文科\"])\n",
    "print (\"文科最低分：\",score_min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0_level_0  Unnamed: 0_level_1    2018\n",
       "一本分数线               文科                     576\n",
       "                    理科                     532\n",
       "二本分数线               文科                     488\n",
       "                    理科                     432\n",
       "Name: 0, dtype: int64"
      ]
     },
     "execution_count": 193,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0_level_0  Unnamed: 0_level_1    2012.000000\n",
       "一本分数线               文科                     541.615385\n",
       "                    理科                     521.153846\n",
       "二本分数线               文科                     487.923077\n",
       "                    理科                     464.384615\n",
       "dtype: float64"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
